import re
from openpyxl import load_workbook
def main():
    #设置excel文件目录
    excel = load_workbook(r'G:\横向课题\地质系统\我的任务\隐患点拓展表设计\隐患点拓展表.xlsx')
    #设置excel文件的子表
    #table = excel.get_sheet_by_name(r'不稳定斜坡')#通过名称设定
    table = excel.worksheets[4]

    rows = table.max_row  # 获取行数
    cols = table.max_column  # 获取列数

    #设置数据表中的数据名称列号
    cols_name=2

    #设置数据表中数据格式列号
    cols_type=3

    #设置表名称(小写字母)
    #table_name = 'pp_expand_slope'
    #table_name='pp_expand_sliding'
    #table_name='pp_expand_collapse'
    #table_name='pp_expand_debris'
    table_name='pp_expand_fissure'


    #print(table.cell(row=2, column=2).value[0])
    #将sql语句写入test.txt
    with open("result.txt", "w") as f:
        with open("insert.txt", "w") as insert:
            #sql语句头信息
            str_start='''
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `'''+table_name+'`;'+'''
CREATE TABLE `'''+table_name+'`(\n'

            #写入头
            f.write(str_start)

            for i in range(1,rows+1):
                #获取指定单元格信息
                value=table.cell(row=i, column=cols_name).value
                type=table.cell(row=i, column=cols_type).value

                if(value==None):
                    continue
                #VARCHAR类型
                elif(type[0]=='V'):
                    num=re.findall(r"\d+",type)
                    str='`'+value+'`'+'varchar('+num[0]+') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL'
                    f.write(str)
                    insert.write("\'1\'")
                #DOUBLE类型
                elif(type[0]=='D'):
                    str = '`' + value + '`' + 'double NOT NULL'
                    f.write(str)
                    insert.write('1.0')
                #INTEGER类型
                elif (type[0] == 'I'):
                    str = '`' + value + '`' + 'int(0) NOT NULL'
                    f.write(str)
                    insert.write('1')
                #其他类型
                else:
                    print('未知类型:',value,type)
                    continue
                #每行需要加入的末尾，但是最后一行除外
                if(i!=rows):
                    f.write(',\n')
                    insert.write(',')
            #sql末尾
            str_end='''
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
'''
            #写入末尾
            f.write(str_end)

if __name__ == '__main__':
    main()

